﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using ARMS.BaseClasses;
using System.ComponentModel.DataAnnotations;

namespace ARMS.Models
{
    #region Customer
    public class Customer
    {
        public Customer() { }
        public Customer(int id, string firstname, string lastname, string address, string email, string mobile, string telephone, string memberno, string model, int shopID)
        {
            this.id = id;
            this.Firstname = firstname;
            this.Lastname = lastname;
            this.Address = address;
            this.Email = email;
            this.Mobile = mobile;
            this.MemberNo = memberno;
            this.Model = model;
            this.shopid = shopid;
        }
        [Required]
        [Display(Name = "Customer ID *")]
        public int id { get; set; }
        [Required]
        [Display(Name = "First Name *")]
        public string Firstname { get; set; }
        [Required]
        [Display(Name = "Last Name *")]
        public string Lastname { get; set; }
        [Display(Name = "Address")]
        public string Address { get; set; }
        [Display(Name = "Email")]
        public string Email { get; set; }
        [Display(Name = "Mobile")]
        public string Mobile { get; set; }
        [Display(Name = "Telephone")]
        public string Telephone { get; set; }
        [Display(Name = "Model")]
        public string Model { get; set; }
        [Display(Name = "Membership number")]
        public string MemberNo { get; set; }
        public int shopid { get; set; }
    }
    #endregion

    #region Customer Model
    public class CustomerModel : ARMS.BaseClasses.BaseModel
    {
        #region Create Customer
        public int createCustomer(Customer customer)
        {

            //                                                                 0        1        2       3       4       5      6       7     8
            string formatedSQL = String.Format(@"INSERT INTO dbo.customer (firstname,lastname,address,mobile,telephone,email,memberno,model,shopid) 
                VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8})",
                customer.Firstname,   //0
                customer.Lastname,    //1
                customer.Address,     //2
                customer.Mobile,
                customer.Telephone,
                customer.Email,
                customer.MemberNo,    //6
                customer.Model,
                customer.shopid
            );
            int rowsAffected = 0;
            try
            {
                rowsAffected = Execute(formatedSQL);
            }
            catch (Exception e)
            {
                throw new Exception("Error creating customer record", e);
            }

            return rowsAffected;
        }
        #endregion

        #region Update Customer
        public int updateCustomer(Customer customer)
        {
            //create sql for update customer
            string formatedSQL = String.Format("UPDATE customer SET firstname='{0}',lastname='{1}',address='{2}',mobile='{3}',telephone='{4}',email='{5}',memberno='{6}' WHERE id={7}",
                customer.Firstname,
                customer.Lastname,
                customer.Address,
                customer.Mobile,
                customer.Telephone,
                customer.Email,
                customer.MemberNo,
                customer.id);

            int rowsAffected = 0;
            try
            {
                rowsAffected = Execute(formatedSQL);
            }
            catch (Exception e)
            {
                throw new Exception("Error updating customer record", e);
            }

            return rowsAffected;
        }
        #endregion

        #region Delete Customer
        public int deleteCustomer(int id)
        {
            //create sql for delete customer
            string formatedSQL = String.Format("DELETE FROM customer WHERE id= {0}", id);
            int affectedRows = Execute(formatedSQL);
            return affectedRows;
        }
        #endregion

        public Customer getCustomer(int id = 0, string memberno = null)
        {
            string select = @"SELECT * FROM customer";
            string where = "";

            if (id != Helper.DummyValue) where += String.Format(@" id={0}", id);
            
            if (memberno != null) where += (where.Length>0)? 
                String.Format(" AND memberno='{0}'", memberno): 
                String.Format(" memberno={0)", memberno);

            string sql = (where.Length > 0) ? select + " WHERE " + where : select;

            DataTable data = new DataTable();
            Customer customer = new Customer();

            try
            {
                data = OpenTable(sql);

                if (data.Rows.Count < 1)
                {
                    throw new Exception("Customer not found!");
                }

                DataRow r = data.Rows[0];
                customer.id = Convert.ToInt32(r["id"].ToString());
                customer.Firstname = r["firstname"].ToString();
                customer.Lastname = r["lastname"].ToString();
                customer.MemberNo = r["memberno"].ToString();
                customer.Telephone = r["telephone"].ToString();
                customer.Mobile = r["mobile"].ToString();
                customer.Address = r["address"].ToString();

                return customer;
            }
            catch (Exception e)
            {
                throw new Exception("Error reading customer record", e);
            }
        }
        public DataTable FindCustomer(int shopid, string memberno = "", string likename = "")
        {
            string select = @"SELECT * FROM customer";
            string where = String.Empty;

            if (memberno == null) memberno = String.Empty;
            if (likename == null) likename = String.Empty;

            if (shopid > 0) where += String.Format(@" shopid={0}", shopid);

            if (memberno.Trim().Length > 0) where += (where.Trim().Length > 0) ?
                String.Format(@" AND memberno LIKE '{0}'", memberno) :
                String.Format(@" memberno LIKE '{0}'", memberno);


            if (memberno.Trim().Length == 0 && likename.Trim().Length > 0) where += (where.Trim().Length > 0) ?
                String.Format(@" AND firstname LIKE '{0}%' OR lastname LIKE '{0}%'", likename.Trim()) :
                String.Format(@" firstname LIKE '{0}%' OR lastname LIKE '{0}%'", likename.Trim());

            string sql = (where.Length > 0) ? select + " WHERE " + where : select;

            DataTable data = new DataTable();

            try
            {
                data = OpenTable(sql);
            }
            catch (Exception e)
            {
                throw new Exception("Error reading customer record", e);
            }

            return data;
        }
        #region List Customer
        public DataTable getCustomers(int shopid)
        {
            string select = @"SELECT * FROM customer";
            string where = @"";

            where += String.Format(" shopid = {0}", shopid);

            string sql = (where.Length > 0) ? select + " WHERE " + where : select;
            sql += " ORDER BY firstname, lastname";

            DataTable data = new DataTable();
            data = OpenTable(sql);
            return data;
        }
        #endregion
    }
    #endregion
}